package wpwork.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import wpwork.base.mapper.SuperMapper;
import wpwork.entity.GpsInfoEntity;
import wpwork.entity.WorkResourcesEntity;
import wpwork.model.GpsInfoVo;
import wpwork.model.GpsInfoVo2;

import java.util.List;

public interface GpsInfoMapper extends SuperMapper<GpsInfoEntity> {

    @Results({
            @Result(property = "userid",     column = "f_userid"),
            @Result(property = "type",       column = "f_type"),
            @Result(property = "lng",        column = "f_lng"),
            @Result(property = "lat",        column = "f_lat"),
            @Result(property = "distance",   column = "f_distance"),
            @Result(property = "remark",     column = "f_remark"),
            @Result(property = "remark1",    column = "f_remark1"),
            @Result(property = "remark2",    column = "f_remark2"),
            @Result(property = "time",       column = "f_time"),
            @Result(property = "tenantId",   column = "f_tenant_id"),
            @Result(property = "realName",   column = "f_real_name")
    })
    @Select("SELECT \n" +
            "    g.*,\n" +
            "    u.f_real_name\n" +
            "FROM (\n" +
            "    SELECT \n" +
            "        *,\n" +
            "        ROW_NUMBER() OVER (\n" +
            "            PARTITION BY f_userid \n" +
            "            ORDER BY f_time DESC\n" +
            "        ) AS rn\n" +
            "    FROM ksh_gpsinfo\n" +
            "    WHERE f_type = 0\n" +
            ") g\n" +
            "LEFT JOIN base_user u ON g.f_remark2 = u.f_id\n" +
            "WHERE g.rn = 1\n" +
            "ORDER BY g.f_time DESC;")
    List<GpsInfoVo> positioningStatistics();



    @Select({
            "<script>",
            "SELECT ",
            "    b.f_group_id AS groupid,",
            "    a.f_name AS groupname,",
            "    d.f_account,",
            "    d.f_real_name,",
            "    SUM(g.f_distance) AS total_distance",
            "FROM ",
            "    ksh_group_table a ",
            "    INNER JOIN ksh_user_group_relation b ON a.f_id = b.f_group_id",
            "    INNER JOIN base_user d ON b.f_user_id = d.f_id",
            "    INNER JOIN (",
            "        SELECT f_remark2 AS userid, f_distance ",
            "        FROM ksh_gpsinfo ",
            "        <where>",
            "            <if test='startTime != null and startTime != \"\"'>",
            "                f_time >= #{startTime}::timestamp",
            "            </if>",
            "            <if test='endTime != null and endTime != \"\"'>",
            "                <if test='startTime != null and startTime != \"\"'> AND </if>",
            "                f_time &lt;= #{endTime}::timestamp",
            "            </if>",
            "        </where>",
            "    ) g ON d.f_id = g.userid",
            "GROUP BY ",
            "    b.f_group_id, a.f_name, d.f_account, d.f_real_name",
            "ORDER BY groupid, d.f_real_name",
            "</script>"
    })
    @Results({
            @Result(property = "groupid", column = "groupid"),
            @Result(property = "groupname", column = "groupname"),
            @Result(property = "account", column = "f_account"),
            @Result(property = "realName", column = "f_real_name"),
            @Result(property = "totalDistance", column = "total_distance")
    })
    List<GpsInfoVo2> getGpsSummaryByTimeRange(
            @Param("startTime") String startTime,
            @Param("endTime") String endTime
    );


    @Select({
            "<script>",
            "SELECT ",
            "    combined.userid,",
            "    u.f_real_name as realName,",
            "    combined.type,",
            "    combined.lng,",
            "    combined.lat,",
            "    combined.distance,",
            "    combined.remark,",
            "    combined.remark1,",
            "    combined.remark2,",
            "    combined.time,",
            "    combined.tenantId",
            "FROM (",
            "    SELECT ",
            "        f_userid as userid,",
            "        f_type as type,",
            "        f_lng as lng,",
            "        f_lat as lat,",
            "        f_distance as distance,",
            "        f_remark as remark,",
            "        f_remark1 as remark1,",
            "        f_remark2 as remark2,",
            "        f_time as time,",
            "        f_tenant_id as tenantId",
            "    FROM ksh_gpsinfo",
            "    UNION ALL",
            "    SELECT ",
            "        f_userid as userid,",
            "        f_type as type,",
            "        f_lng as lng,",
            "        f_lat as lat,",
            "        f_distance as distance,",
            "        f_remark as remark,",
            "        f_remark1 as remark1,",
            "        f_remark2 as remark2,",
            "        f_time as time,",
            "        f_tenant_id as tenantId",
            "    FROM ksh_gpsinfo_history",
            ") combined",
            "LEFT JOIN base_user u ON combined.remark2 = u.f_id",
            "<where>",
            "    combined.type = 0",
            "    <if test='startTime != null and startTime != \"\"'>",
            "        AND combined.time &gt;= CAST(#{startTime} AS TIMESTAMP)",
            "    </if>",
            "    <if test='endTime != null and endTime != \"\"'>",
            "        AND combined.time &lt;= CAST(#{endTime} AS TIMESTAMP)",
            "    </if>",
            "    <if test='name != null and name != \"\"'>",
            "        AND u.f_real_name LIKE CONCAT('%', #{name}, '%')",
            "    </if>",
            "</where>",
            "ORDER BY combined.time DESC",
            "</script>"
    })
    @Results({
            @Result(property = "userid", column = "userid"),
            @Result(property = "realName", column = "realName"),
            @Result(property = "type", column = "type"),
            @Result(property = "lng", column = "lng"),
            @Result(property = "lat", column = "lat"),
            @Result(property = "distance", column = "distance"),
            @Result(property = "remark", column = "remark"),
            @Result(property = "remark1", column = "remark1"),
            @Result(property = "remark2", column = "remark2"),
            @Result(property = "time", column = "time"),
            @Result(property = "tenantId", column = "tenantId")
    })
    IPage<GpsInfoVo> getListWithJoinTable(Page<GpsInfoVo> page, @Param("startTime") String startTime,
                                          @Param("endTime") String endTime, @Param("name") String name);

}
